package com.export.document.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.List;
import java.util.Map;

public class ExcelUtil {
    public static void createSheet(XSSFWorkbook wb, String sheetName, String sheetNameEn, String[] titles1, String[] title2, List<Map<String, Object>> values) {
        XSSFSheet sheet = wb.createSheet(sheetNameEn);
        for (int i = 0; i < 5; i++) {
            sheet.setColumnWidth(i, 6400);
        }
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont titleFont = wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 11);
        titleStyle.setFont(titleFont);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFCellStyle contextStyle = wb.createCellStyle();
        contextStyle.setFont(titleFont);
        contextStyle.setBorderRight(BorderStyle.THIN);
        contextStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        contextStyle.setBorderBottom(BorderStyle.THIN);
        contextStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //第一行,表头
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        cell = row.createCell(0);
        cell.setCellValue(titles1[0]);
        cell.setCellStyle(titleStyle);
        cell = row.createCell(1);
        cell.setCellValue(sheetNameEn);
        cell.setCellStyle(contextStyle);
        cell = row.createCell(2);
        cell.setCellValue(titles1[1]);
        cell.setCellStyle(titleStyle);
        cell = row.createCell(3);
        cell.setCellValue(sheetName);
        cell.setCellStyle(contextStyle);
        //第二行,表头
        row = sheet.createRow(1);
        for (int i = 0; i < title2.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(titleStyle);
        }
        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = sheet.createRow(i + 2);
            Map<String, Object> map = values.get(i);
            cell = row.createCell(0);
            cell.setCellValue(map.get("COLUMN_NAME").toString());
            cell.setCellStyle(contextStyle);

            cell = row.createCell(1);
            cell.setCellValue(map.get("DATA_TYPE").toString());
            cell.setCellStyle(contextStyle);

            cell = row.createCell(2);
            if (map.get("CHARACTER_MAXIMUM_LENGTH") != null) {
                cell.setCellValue(map.get("CHARACTER_MAXIMUM_LENGTH").toString());
            } else if (map.get("NUMERIC_PRECISION") != null) {
                if (map.get("NUMERIC_SCALE") != null) {
                    cell.setCellValue(map.get("NUMERIC_PRECISION").toString() + "," + map.get("NUMERIC_SCALE").toString());
                } else {
                    cell.setCellValue(map.get("NUMERIC_PRECISION").toString());
                }
            } else {
                cell.setCellValue("");
            }
            cell.setCellStyle(contextStyle);

            cell = row.createCell(3);
            cell.setCellValue(map.get("IS_NULLABLE").toString());
            cell.setCellStyle(contextStyle);

            cell = row.createCell(4);
            cell.setCellValue(map.get("COLUMN_COMMENT").toString());
            cell.setCellStyle(contextStyle);
        }
    }

    /**
     * 生成目录
     *
     * @param wb
     * @param tables
     */
    public static void createCatalogue(XSSFWorkbook wb, List<Object[]> tables) {
        XSSFSheet sheet = wb.createSheet("目录");
        for (int i = 0; i < 2; i++) {
            sheet.setColumnWidth(i, 6400);
        }

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont titleFont = wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 11);
        titleStyle.setFont(titleFont);
        titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFCellStyle linkStyle = wb.createCellStyle();
        XSSFFont cellFont = wb.createFont();
        cellFont.setUnderline((byte) 1);
        cellFont.setColor(IndexedColors.BLUE.getIndex());
        linkStyle.setFont(cellFont);

        //第一行,表头
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        cell = row.createCell(0);
        cell.setCellValue("表名");
        cell.setCellStyle(titleStyle);
        cell = row.createCell(1);
        cell.setCellValue("描述");
        cell.setCellStyle(titleStyle);

        for (int i = 0; i < tables.size(); i++) {
            row = sheet.createRow(i + 1);
            Object[] objArr = tables.get(i);
            cell = row.createCell(0);
            String tbName = objArr[0].toString();
            cell.setCellValue(tbName);
            cell.setCellFormula("Hyperlink(\"#" + tbName + "!A1\",\"" + tbName + "\")");
            cell.setCellStyle(linkStyle);

            cell = row.createCell(1);
            cell.setCellValue(objArr[1].toString());
        }
    }
}
